﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class NhanVienDAO
    {
        public List<NhanVienDTO> LayDanhSachNhanVien()
        {
            List<NhanVienDTO> ds = new List<NhanVienDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayDanhSachNhanVien";
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                NhanVienDTO dto = new NhanVienDTO();
                dto.MaNhanVien = dr.GetInt32(0);
                dto.TenNhanVien = dr.GetString(1);
                dto.NgaySinh = dr.GetDateTime(2);
                dto.DiaChi = dr.GetString(3);
                dto.GioiTinh = dr.GetString(4);
                dto.SoDienThoai = dr.GetString(5);
                dto.LoaiNhanVien = dr.GetInt32(6);
                dto.TenDangNhap = dr.GetString(7);
                dto.MatKhau = dr.GetString(8);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public NhanVienDTO LayThongTinNhanVien(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayThongTinNhanVien";
            cm.Parameters.Add("@MaNhanVien", SqlDbType.Int).Value = ma;
            SqlDataReader dr = cm.ExecuteReader();
            NhanVienDTO dto = new NhanVienDTO();
            dr.Read();
            dto.MaNhanVien = dr.GetInt32(0);
            dto.TenNhanVien = dr.GetString(1);
            dto.NgaySinh = dr.GetDateTime(2);
            dto.DiaChi = dr.GetString(3);
            dto.GioiTinh = dr.GetString(4);
            dto.SoDienThoai = dr.GetString(5);
            dto.LoaiNhanVien = dr.GetInt32(6);
            dto.TenDangNhap = dr.GetString(7);
            dto.MatKhau = dr.GetString(8);
            dr.Close();
            conn.Close();
            return dto;
        }

        //new
        public string LayTenNhanVienTheoMa(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayTenNhanVienTheoMa";
            cm.Parameters.Add("@MaNhanVien", SqlDbType.Int).Value = ma;
            string ten = cm.ExecuteScalar().ToString();
            conn.Close();
            return ten;
        }

        public void ThemNhanVien(NhanVienDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_ThemNhanVien";        
            cm.Parameters.Add("@TenNhanVien", SqlDbType.NVarChar).Value = dto.TenNhanVien;
            cm.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value = dto.NgaySinh;
            cm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = dto.DiaChi;
            cm.Parameters.Add("@GioiTinh", SqlDbType.NVarChar).Value = dto.GioiTinh;
            cm.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar).Value = dto.SoDienThoai;
            cm.Parameters.Add("@LoaiNhanVien", SqlDbType.Int).Value = dto.LoaiNhanVien;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void XoaNhanVien(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_XoaNhanVien";
            cm.Parameters.Add("@MaNhanVien", SqlDbType.Int).Value = ma;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatNhanVien(NhanVienDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_CapNhatNhanVien";
            cm.Parameters.Add("@MaNhanVien", SqlDbType.Int).Value = dto.MaNhanVien;
            cm.Parameters.Add("@TenNhanVien", SqlDbType.NVarChar).Value = dto.TenNhanVien;
            cm.Parameters.Add("@NgaySinh", SqlDbType.DateTime).Value = dto.NgaySinh;
            cm.Parameters.Add("@DiaChi", SqlDbType.NVarChar).Value = dto.DiaChi;
            cm.Parameters.Add("@GioiTinh", SqlDbType.NVarChar).Value = dto.GioiTinh;
            cm.Parameters.Add("@SoDienThoai", SqlDbType.NVarChar).Value = dto.SoDienThoai;
            cm.Parameters.Add("@LoaiNhanVien", SqlDbType.Int).Value = dto.LoaiNhanVien;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public bool KiemTraDangNhap(string user, string pass, ref int ma, ref string ten, ref int loainhanvien)
        {
            bool kt = false;
            SqlConnection conn = null;
            try
            {
                String sql = "select * from NHANVIEN where TenDangNhap = '" + user + "' and MatKhau =  '" + pass + "'";
                conn = DataProvider.ConnectDB();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ma = reader.GetInt32(0);
                    ten = reader.GetString(1);
                    loainhanvien = reader.GetInt32(6);
                }
                if (ma != 0)
                    kt = true;
            }
            catch (NullReferenceException)
            {
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return kt;
        }
    }
}
